import pathlib

import xlrd


def read_excel():
    # 文件位置
    ExcelFile = xlrd.open_workbook(r'C:\Users\Administrator\Desktop\python\test.xlsx')

    # 获取目标EXCEL文件sheet名
    print(ExcelFile.sheet_names())

    # ------------------------------------

    # 若有多个sheet，则需要指定读取目标sheet例如读取sheet2

    sheet3_name = ExcelFile.sheet_names()[2]

    # ------------------------------------

    # 获取sheet内容【1.根据sheet索引2.根据sheet名称】

    # sheet=ExcelFile.sheet_by_index(1)

    sheet = ExcelFile.sheet_by_name(sheet3_name)

    # 打印sheet的名称，行数，列数

    print(sheet.name, sheet.nrows, sheet.ncols)

    d = {}
    st = set()
    for i in range(sheet.nrows):
        bankNo = str(int(sheet.cell(i, 0).value))
        bankName = str(sheet.cell(i, 1).value.encode("utf-8"), encoding="utf-8")
        if bankNo not in st:
            st.add(bankNo)
            d[bankNo] = bankName
        else:
            pass

    print(len(st))
    # print(d)

    hp = pathlib.Path.cwd()
    # print(cp)
    # print(hp)
    # str_path = fr"C:\Users\Administrator\Desktop\abc\abc.txt"
    # wp = pathlib.Path(str_path)
    # print(wp)

    np = hp / 'test'
    print(np)
    with np.open(mode='w', encoding="utf-8") as f:
        for k, v in d.items():
            f.write(f"{k}, {v} \n")

    # 获取整行或者整列的值

    # rows = sheet.row_values(2)  # 第三行内容
    #
    # cols = sheet.col_values(1)  # 第二列内容
    #
    # print(cols, rows)

    # 获取单元格内容

    # print(sheet.cell(1, 0).value.encode('utf-8'))
    #
    # print(sheet.cell_value(1, 0).encode('utf-8'))
    #
    # print(sheet.row(1)[0].value.encode('utf-8'))

    # 打印单元格内容格式
    #
    # print(sheet.cell(1, 0).ctype)


if __name__ == '__main__':
    read_excel()
    # ff = "中国人民银行"
